package com.qunar.support.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;

import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;

import com.qunar.support.util.DBHelper;

public class UserDao {
	static Logger logger = Logger.getLogger(UserDao.class);
	private Connection con;
	public static int DELETED = 98;
	
	/**用户状态1表示有效，2表示禁用，-1表示逻辑删除
	 * */
	/**
	 * 若验证通过，返回其uid
	 * 
	 * @param username
	 * @param password
	 * @return
	 */
	public int validate(String username, String password) {
		try {
			con = DBHelper.getConnection();
			String query = "select id from user where login_name=? and password=?";
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			ResultSet rs = pstmt.executeQuery();
			int uid = -1;
			while (rs.next()) {
				uid = rs.getInt(1);
			}
			logger.info((getClass() + ":" + uid));
			return uid;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 计算过期天数
	 * 
	 * @param uid
	 * @return
	 */
	public int overdueDay(int uid) {
		try {
			con = DBHelper.getConnection();
			String query = "select last_password_modify_time from user where id=?";
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setInt(1, uid);
			ResultSet rs = pstmt.executeQuery();
			Date date = null;
			while (rs.next()) {
				date = rs.getDate(1);
			}
			long od = (date.getTime() - System.currentTimeMillis())
					/ (24 * 60 * 60 * 1000);
			int result = (int) (od + 30);
			logger.info("过期天数为" + result);
			return result;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 返回所有用户
	 */
	public JSONArray getAllUser() {
		String query = "select login_name,real_name,status,last_password_modify_time from user where status != -1";
		try {
			con = DBHelper.getConnection();
			PreparedStatement pstmt=con.prepareStatement(query);
			ResultSet rs=pstmt.executeQuery();
			JSONArray arr=new JSONArray();
			while(rs.next()){
				JSONObject json = new JSONObject();
				json.put("login_name", rs.getString(1));
				json.put("realname", rs.getString(2));
				json.put("status", rs.getString(3));
				Date d=rs.getDate(4);
				logger.info(d);
				int overdue=(int) ((d.getTime()-System.currentTimeMillis())/(24*60*60*1000)+30);
				json.put("overdue", overdue);
				arr.put(json);
			}
			return arr;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取指定用户的状态
	 * 
	 * @param username
	 * @return
	 */
	public int active(String username) {
		try {
			con = DBHelper.getConnection();
			String query = "select status from user where login_name = ?";
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1, username);
			ResultSet rs = pstmt.executeQuery();
			int status = 3;
			while (rs.next()) {
				status = rs.getInt(1);
			}
			return status;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 3;
	}

	/**
	 * 设置指定用户的状态(禁用/启用/删除)
	 * @param uid
	 * @param status
	 * @author ziqiang.deng
	 */
	public void operateUser(int uid, int status) {
		String sql = "UPDATE user SET status = ? where id = ?;";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = con.prepareStatement( sql );
			pstmt.setInt(1, status);
			pstmt.setInt(2, uid);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ;
	}

	/**
	 * 重置密码
	 * @param uid
	 * @param sb
	 * @author ziqiang.deng
	 */
	public void resetPassword(int uid, String sb) {
		String sql = "UPDATE user SET password = ? where id = ?;";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = con.prepareStatement( sql );
			pstmt.setString(1, sb);
			pstmt.setInt(2, uid);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ;
	}

	/**
	 * 增加一个用户，代理商或审核人员
	 * @param username
	 * @param realname
	 * @param password
	 * @param ...
	 * @author ziqiang.deng
	 */
	public void addOperator(String username, String realname, String password, String phone, String email, int role) {
		String sql = "INSERT INTO user (login_name, real_name, password, status, role, phone, email, last_password_modify_time, address) values "
				+ "(?, ?, ?, ?, ?, ?, ?, ?, ?);";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = con.prepareStatement( sql );
			pstmt.setString(1, username);
			pstmt.setString(2, realname);
			pstmt.setString(3, password);
			pstmt.setInt(4, 1);
			pstmt.setInt(5, role);
			pstmt.setString(6, phone);
			pstmt.setString(7, email);
			long currentTime = System.currentTimeMillis();
			Time time = new Time( currentTime );
			Date date = new Date( currentTime );
			pstmt.setString(8, date.toString() + " " + time.toString());
			pstmt.setString(9, "");
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ;
	}
	/**
	 * 判断一个用户名是否在数据库中存在（包括状态为逻辑删除的用户）
	 * @param name
	 * @author ziqiang.deng
	 */
	public boolean contains(String name){
		String sql = "SELECT count(*) FROM user WHERE login_name = ?;";
		try {
			this.con = DBHelper.getConnection();
			PreparedStatement pstmt = con.prepareStatement( sql );
			pstmt.setString(1, name);
			
			ResultSet rs = pstmt.executeQuery();
			int n = -1;
			while( rs.next() ){
				n = rs.getInt(1);
			}
			if( 0 != n )//登录用户名已存在
				return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

}
